#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <ActiveQt/QAxObject>
#include <QMessageBox>
#include <QDebug>
#include <QFileDialog>
#include <QDesktopServices>

#define SAM_NAME_HEAD "Sample Name"
#define GENE_NAME_HEAD "Target Name"
#define CT_MEAN_HEAD  "Ct Mean"
#define CT_SD_HEAD "Ct SD"

#define name_index 0
#define gene_index 1
#define ct_mean_index 2
#define ct_sd_index 3

#define SHEET_NAME "Results"
#define OUTPUT_SHEET_NAME "New_Result"


MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
}

MainWindow::~MainWindow()
{
    delete ui;
}

void error_exit(QString info){
    QMessageBox msgBox;
    msgBox.setText(info);
    msgBox.exec();
    exit(0);
}

void output_warning(QString info){
    qDebug() << info;
}

QString open_excel_file(){
    QString file_full_name =  QFileDialog::getOpenFileName(NULL, QObject::tr("open file"), " ", QObject::tr("Excel file(*.xlsx *.xls)"));
    return  file_full_name;
}

QList<QList<QVariant> > read_raw_data(QAxObject * used_range){
    QVariant var;
    var = used_range->dynamicCall("Value");
    QVariantList varRows = var.toList();
    QList<QList<QVariant> > res;
    if(varRows.isEmpty())
    {
        error_exit("There is no data on the sheet: " SHEET_NAME);
    }
    const int rowCount = varRows.size();
    QVariantList rowData;
    for(int i=0;i<rowCount;++i)
    {
        rowData = varRows[i].toList();
        res.push_back(rowData);
    }
    return res;
}

QList<QList<QString> > get_vaild_data(QList<QList<QVariant> > raw_data){
    QList<QList<QString> > data;
    bool flag = false;
    QList<int> vaild_col;
    QList<QString> line_list;
    int row = raw_data.length();
    int col = raw_data[0].length();
    bool record_invaild_flag = false;
    QString line_string;
    int col_num_sample_name, col_num_gene_name, col_num_ct_mean, col_num_ct_sd;
    for (int i=0; i< row; i++){
        if (raw_data[i][0].toString() == "Well"){
            for (int j=0; j< col;j++){
                QString col_name = raw_data[i][j].toString().trimmed();
                if ((col_name == SAM_NAME_HEAD) || (col_name == GENE_NAME_HEAD) \
                        || (col_name == CT_MEAN_HEAD) || (col_name == CT_SD_HEAD)){
                    vaild_col.append(j);
                }
                if (col_name == SAM_NAME_HEAD)
                    col_num_sample_name = j;
                if (col_name == GENE_NAME_HEAD)
                    col_num_gene_name = j;
                if (col_name == CT_MEAN_HEAD)
                    col_num_ct_mean = j;
                if (col_name == CT_SD_HEAD)
                    col_num_ct_sd = j;
            }
            flag = true;
            continue;
        }
        if (flag == true){
            if (raw_data[i][0].toString().trimmed() == "" || \
                raw_data[i][0].toString().trimmed()== "Analysis Type" ){
                flag = false;
                break;
            }
            else {
                line_list.clear();
                line_string = "";
                for (int j=0; j< raw_data[i].length(); j++){
                    if (vaild_col.contains(j)){
                        if (raw_data[i][j].toString().trimmed()=="" && j!= col_num_ct_sd){
                            record_invaild_flag = true;
                        }
                        line_list.append(raw_data[i][j].toString());
                    }
                    line_string +=  raw_data[i][j].toString() + QString("  ");
                }
                if (record_invaild_flag){
                    record_invaild_flag = false;
                    output_warning(QString("Find invaild record:") + line_string);
                }
                else{
                    data.append(line_list);
                }
            }
        }
    }
    return data;
}
class DataPair{
public:
    QString ct_mean;
    QString ct_sd;
    DataPair(){
        this->ct_mean = "";
        this->ct_sd = "";
    }

    DataPair(QString str1, QString str2){
        this->ct_mean = str1;
        this->ct_sd = str2;
    }

};

class Records
{
    public:
    QMap<QString,QMap<QString, DataPair> > record_map;

    Records(){

    }

    void add_record(QString name, QString gene, QString ct_mean, QString ct_sd){
        if ((name.trimmed()=="NTC")){
            return;
        }
        if((name.trimmed()=="") || (ct_mean.trimmed()=="")){
            return;
        }
        if (record_map.contains(name)){
            record_map[name][gene] = DataPair(ct_mean,ct_sd);
        }
        else{
            QMap<QString, DataPair> tmp_map;
            tmp_map[gene] = DataPair(ct_mean,ct_sd);
            record_map[name] = tmp_map;
        }
    }

    QString get_record_ct_mean(QString name, QString gene){
        if (record_map.contains(name) && record_map[name].contains(gene)){
            return record_map[name][gene].ct_mean;
        }
        else{
            return "";
        }
    }

    QString get_record_ct_sd(QString name, QString gene){
        if (record_map.contains(name) && record_map[name].contains(gene)){
            return record_map[name][gene].ct_sd;
        }
        else{
            return "";
        }
    }

};

QList<QList<QString> > get_formated_data(QList<QList<QString> > data){
    QList<QList<QString> > formated_data;
    QList<QString> gene_list;
    QList<QString> name_list;
    Records records;
    QString name,gene,ct_mean,ct_sd;
    for (int i=0; i<data.length(); i++){
        name = data[i][name_index];
        gene = data[i][gene_index];
        ct_mean = data[i][ct_mean_index];
        ct_sd = data[i][ct_sd_index];
        records.add_record(name,gene,ct_mean,ct_sd);
        if (!name_list.contains(name) && name!="NTC" && name.trimmed()!=""){
            name_list.append(name);
        }
        if (!gene_list.contains(gene) && gene.trimmed()!=""){
            gene_list.append(gene);
        }
    }

    QList<QString> head_list;
    head_list.append("Sample");
    for (int i=0; i<gene_list.length(); i++){
        head_list.append(gene_list[i]);
        head_list.append("Ct_SD");
    }
    formated_data.append(head_list);
    for (int i=0; i<name_list.length(); i++){
        QList<QString> cur_list;
        cur_list.append(name_list[i]);
        for (int j=0; j<gene_list.length(); j++){
            cur_list.append(records.get_record_ct_mean(name_list[i],gene_list[j]));
            cur_list.append(records.get_record_ct_sd(name_list[i],gene_list[j]));
        }
        formated_data.append(cur_list);
    }
    return formated_data;
}


void write_result(QList<QList<QString> > formated_data, QAxObject * workbook){
    QAxObject * worksheets = workbook->querySubObject("WorkSheets");
    QAxObject * old_worksheet = worksheets->querySubObject("Item(QString)", OUTPUT_SHEET_NAME);
    if (old_worksheet != NULL){
        old_worksheet->dynamicCall("delete");
//        old_worksheet = workbook->querySubObject("WorkSheets(QString)", OUTPUT_SHEET_NAME);
//        if (old_worksheet!=NULL){
//            error_exit("删除失败！");
//        }
    }
    QAxObject * first_worksheet = worksheets->querySubObject("Item(int)",1);
    first_worksheet->dynamicCall("Activate()");
    worksheets->dynamicCall("Add()");
    QAxObject * worksheet = worksheets->querySubObject("Item(int)",1);
    if (worksheet != NULL){
        worksheet->setProperty("Name",OUTPUT_SHEET_NAME);
    }
//    else{
//        qDebug() << "Can't find the sheet to rename!";
//    }

    /* we get the worksheet, write data to it now. */
    QAxObject * temp_cell;
    for (int i=0; i< formated_data.length(); i++){
        for (int j=0; j< formated_data[i].length(); j++){
            temp_cell = worksheet->querySubObject("cells(int,int)",i+1,j+1);
            //temp_cell->setProperty("NumberFormatLocal","@");
            temp_cell->setProperty("Value2", formated_data[i][j]);
        }
    }

}
void MainWindow::output_log(QString log){
     ui->text_out->append(log);
     ui->text_out->repaint();
}
void MainWindow::clear_log(){
     ui->text_out->setText("");
     ui->text_out->repaint();
}

void MainWindow::on_btn_open_clicked()
{
    clear_log();
    output_log("Selecting the file...");
    QString path = open_excel_file();
    if (path.trimmed()==""){
        output_log("User cancel");
        return;
    }
    output_log("Opening the document...");
    QAxObject * excel = new QAxObject("Excel.Application");
    excel->setProperty("DisplayAlerts",false);
    excel->setProperty("Visible",false);
    QAxObject * workbooks = excel->querySubObject("WorkBooks");
    QAxObject * workbook = workbooks->querySubObject("Open(QString)", path);
    output_log("Searching the "SHEET_NAME" sheet...");
    QAxObject * worksheet = workbook->querySubObject("WorkSheets(QString)", SHEET_NAME);
    if(worksheet == NULL){
        error_exit("Error: Can't find the sheet: " SHEET_NAME);
    }
    //worksheet->dynamicCall("Activate()");
    QAxObject * usedRange = worksheet->querySubObject("UsedRange");
    QList<QList<QVariant> > raw_data = read_raw_data(usedRange);
    output_log("Reading & analysing the data...");
    QList<QList<QString> > data = get_vaild_data(raw_data);
    QList<QList<QString> > formated_data = get_formated_data(data);
    output_log("Writing the result...");
    write_result(formated_data, workbook);
    //qDebug() << "DATA:\n" << data ;
    //qDebug() << "FORMATED DATA:\n" << formated_data ;
    output_log("Save&Close the file...");
    workbook->dynamicCall("Save()");
    workbook->dynamicCall("Close()");
    excel->dynamicCall("Quit()");
    delete usedRange;
    delete worksheet;
    delete workbook;
    delete workbooks;
    delete excel;
    output_log("Job done!");
    if (ui->ckbox_open_after_finish->isChecked()){
        if(!QDesktopServices::openUrl(QUrl::fromLocalFile(path))){
            output_log("Warning: Open file failed, please open it manually.");
        }
    }
}

void MainWindow::on_btn_help_clicked()
{
    clear_log();
    output_log("1. 请保证实验数据在名为\""SHEET_NAME"\"的工作表中");
    output_log("2. \""SAM_NAME_HEAD"\"和\""GENE_NAME_HEAD"\"两列尽量不要使用特殊符号");
    output_log("3. 点击左侧按钮提取实验数据");
    output_log("4. 结果将保存在同文件的\""OUTPUT_SHEET_NAME"\"工作表中，如果已存在，将被覆盖");
    output_log(" ");
    output_log("<font size=2 color=gray>thelxz@163.com<font>");
    output_log("<font size=2 color=gray>Based on Qt5.6<font>");
    output_log("<font size=2 color=green>License: GPLv3<font>");

}
